﻿using System;
using System.Collections.Generic;
using System.Text;
using CSharpHelpers.Encrypts;
using MySql.Data.MySqlClient;

namespace CSharpHelpers.DBAccess
{
    public class Access
    {
        /// <summary>
        /// SQL命令队列
        /// </summary>
        //private static WorkQueue<string> SqlWorkQueue;

        //public Access()
        //{
        //    SqlWorkQueue = new WorkQueue<string>();
        //    SqlWorkQueue.WorkSequential = true;
        //    SqlWorkQueue.UserWork += new UserWorkEventHandler<string>(SqlWorkQueue_UserWork);
        //}

        ///// <summary>
        ///// 执行队列中的对象（SQL Command）
        ///// </summary>
        ///// <param name="sender"></param>
        ///// <param name="e"></param>
        //private static void SqlWorkQueue_UserWork(object sender, WorkQueue<string>.EnqueueEventArgs e)
        //{
        //    MySqlUtil2.ExecuteNonQuery(e.Item, null);
        //}

        /// <summary>
        /// 检查对象是否为Null,如NUll返回Empty
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        protected string CheckNull(object value)
        {
            string s = Convert.ToString(value);
            if (String.IsNullOrEmpty(s))
                return string.Empty;
            else
                return s;
        }

        protected double CheckDoubleNull(object value)
        {
            if (value == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToDouble(value);
            }
        }

        protected byte[] CheckByteArrayNull(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return (byte[])value;
            }
        }

        protected char[] CheckCharArrayNull(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return (char[])value;
            }
        }

        protected char CheckCharNull(object value)
        {
            if (value == DBNull.Value)
            {
                return ' ';
            }
            else
            {
                return Convert.ToChar(value);
            }
        }

        protected int CheckIntNull(object value)
        {
            if (value == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(value);
            }
        }


        protected bool CheckBoolNull(object value)
        {
            if (value == DBNull.Value)
            {
                return false;
            }
            else
            {
                return Convert.ToBoolean(value);
            }
        }

        protected long CheckLongNull(object value)
        {
            if (value == DBNull.Value)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt64(value);
            }
        }

        /// <summary>
        /// 检查对象是否为null
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        protected System.DateTime CheckDatetimeNull(object value)
        {
            System.DateTime dt = DateTime.Now;
            if (value == DBNull.Value)
            {
                return new System.DateTime(1900, 1, 1);
            }
            else
            {
                if (DateTime.TryParse(value.ToString(), out dt))
                {
                    return dt;
                }
                else
                {
                    return new System.DateTime(1900, 1, 1);
                }
            }
        }

        /// <summary>
        /// 获取数据库的时间
        /// </summary>
        /// <returns></returns>
        public System.DateTime GetDBDateTime()
        {
            string sql = "select now()";
            System.DateTime dbTime = DateTime.Now;

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                if (reader.Read())
                {
                    dbTime = Convert.ToDateTime(reader[0]);
                }
            }

            return dbTime;
        }

        /// <summary>
        /// 获取数据库的日期
        /// </summary>
        /// <returns></returns>
        public System.DateTime GetDBDate()
        {
            string sql = "select curdate()";
            System.DateTime dbTime = DateTime.Now;

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                if (reader.Read())
                {
                    dbTime = Convert.ToDateTime(reader[0]);
                }

            }

            return dbTime;
        }

        /// <summary>
        /// 返回最剛剛插入數據的ID  有插入才會有返回值否則返回 0 或者 null
        /// 用GetLastID 這方法 如果多人同時插入會有問題
        /// 基於connection的 一旦關閉就關閉了
        /// @author bell by 2013-07-24
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="conn"> </param>
        /// <returns></returns>
        public int GetCurrentInsertId(string tableName, MySqlConnection conn, MySqlTransaction sqlTran)
        {
            string sql = String.Format("SELECT LAST_INSERT_ID() AS ID FROM  {0} LIMIT 1;", tableName);
            int id = 0;
            using (MySqlDataReader reader = MySqlUtil.GetDataReaderSqlTran(sql, null, conn, sqlTran))
            {
                if (reader.Read())
                {
                    id = Convert.ToInt32(reader["ID"]);
                }

            }

            return id;
        }

        /// <summary>
        /// 获取最后的ID
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int GetLastID(string tableName)
        {
            string sql = String.Format("select ID from {0} order by ID DESC limit 0, 1", tableName);
            int id = 0;
            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                if (reader.Read())
                {
                    id = Convert.ToInt32(reader["ID"]);
                }
            }

            return id;
        }

        protected int GetMeterCode(string hex)
        {
            return Convert.ToInt32(hex, 16);
        }

        protected double GetCent(double amount)
        {
            return amount;
            //return (int)(amount * 100);
        }

        /// <summary>
        /// 数据库是否已连接
        /// </summary>
        /// <returns></returns>
        public bool IsConnectedDB()
        {
            return MySqlUtil.IsConnect(DBConst.DB_DEFAULT);
        }

        ///// <summary>        
        ///// 执行Sql语句操作       
        ///// </summary>     
        //public static void EnqueueSql(string sql)
        //{
        //    SqlWorkQueue.Enqueue(sql);
        //}

        protected string BuildQuote(string sql, string key, string value)
        {
            return sql.Replace(key, "'" + value + "'");
        }

        protected string BuildQuote(string sql, string key, double value)
        {
            return sql.Replace(key, value.ToString());
        }

        protected string BuildQuote(string sql, string key, int value)
        {
            return sql.Replace(key, value.ToString());
        }

        protected string BuildQuote(string sql, string key, System.DateTime value)
        {
            return sql.Replace(key, "'" + value.ToString("yyyy-MM-dd HH:mm:ss") + "'");
        }

        public string GetTimeSQL(System.DateTime start, System.DateTime end, string filedName, string padLeft = "")
        {
            return String.Concat(padLeft, "(", filedName, " between '", start.ToString("yyyy-MM-dd HH:mm:ss"), "' and '", end.ToString("yyyy-MM-dd HH:mm:ss"), "')");
        }

        public string GetTimeSQL_Exclude(System.DateTime start, System.DateTime end, string filedName, string padLeft = "")
        {
            return String.Concat(padLeft, "(", filedName, " > '", start.ToString("yyyy-MM-dd HH:mm:ss"), "' and " + filedName + "< '" + end.ToString("yyyy-MM-dd HH:mm:ss"), "')");
        }

        /// <summary>
        /// 安全過濾SQL字符串
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string Filterstr(string str)
        {
            string strs = str;
            strs = strs.Replace(",", "");
            strs = strs.Replace("'", "");
            //strs = strs.Replace("and", "");
            //strs = strs.Replace("or", "");
            strs = strs.Replace(";", "");
            return strs;
        }

        /// <summary>
        /// 判断是不是有效时间 --add by george 2013-9-23
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool CheckValidTime(System.DateTime dt)
        {
            bool isValid = true;

            System.DateTime dt1 = Convert.ToDateTime("0001-01-01");
            if (dt == dt1)
            {
                isValid = false;
            }
            return isValid;
        }

        protected int GetPageList<T>(List<T> list, int pageCount = 1000)
        {
            if (list == null)
                return 0;
            int count = list.Count;
            count = count % pageCount == 0 ? count / pageCount : count / pageCount + 1;
            return count;
        }

        private static readonly string defaultConn = System.Configuration.ConfigurationManager.ConnectionStrings["DataBase.LocalDB.MySqlConnectionString"].ConnectionString;

        public string BuildConnStr(string databaseName)
        {
            string connStr = GetNewConnStr(defaultConn);
            if (!String.IsNullOrEmpty(databaseName))
            {

            }
            return connStr;
        }

        private static string GetNewConnStr(string oldCon)
        {
            int passwordIndex = 0;
            string oldPassword = string.Empty;
            string[] array = oldCon.Split(';');

            for (int i = 0; i < array.Length; i++)
            {
                if (array[i].IndexOf("password=") > 0)
                {
                    passwordIndex = i;
                    string[] arrayPassword = array[i].Split('=');
                    oldPassword = arrayPassword[1];
                    break;
                }
            }

            string newPasword = AESEncryptHelper.AESDecrypt(oldPassword);
            array[passwordIndex] = string.Format("password={0}", newPasword.Trim());

            StringBuilder sb = new StringBuilder();
            foreach (var s in array)
            {
                sb.Append(s);
                sb.Append(";");
            }

            return sb.ToString();
        }

        public System.DateTime CheckDatetimeNull(object value, System.DateTime defaultValue)
        {
            if (value == DBNull.Value)
            {
                return DateTime.Now;
            }
            else
            {
                System.DateTime mytime;
                try
                {
                    mytime = Convert.ToDateTime(value);
                }
                catch (Exception)
                {
                    mytime = defaultValue;
                }

                return mytime;
            }
        }

        public byte[] CheckByteNull(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return (byte[])value;
            }
        }

        /// <summary>
        /// 報表時間區間查詢
        /// 查詢區間包括本月的最後操作時間
        /// add by hyman
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="filedName"></param>
        /// <param name="padLeft"></param>
        /// <returns></returns>
        public string GetTimeSQL_ExcludeEquals(System.DateTime start, System.DateTime end, string filedName, string padLeft = "")
        {
            return String.Concat(padLeft, "(", filedName, " > '", start.ToString("yyyy-MM-dd HH:mm:ss"), "' and " + filedName + "<= '" + end.ToString("yyyy-MM-dd HH:mm:ss"), "')");
        }

        protected string BuildStringComma(List<string> list)
        {
            string str = String.Empty;
            for (int i = 0; i < list.Count; i++)
            {
                str += (i == list.Count - 1) ? list[i] : list[i] + ",";
            }

            return str;
        }

        protected string BuildStringComma(List<int> list)
        {
            string str = String.Empty;
            for (int i = 0; i < list.Count; i++)
            {
                str += (i == list.Count - 1) ? list[i].ToString() : list[i].ToString() + ",";
            }

            return str;
        }

        public string TransformEncoding(string fromCode, string toCode, string value)
        {
            Encoding from = Encoding.GetEncoding(fromCode);
            Encoding to = Encoding.GetEncoding(toCode);
            byte[] b = from.GetBytes(value);
            return to.GetString(b);
        }

        public static string GetSqlConditionString(string condition, string value)
        {
            string temp = " " + condition + "='" + value + "' and ";
            return value == "" ? "" : temp;
        }

        public static string GetSqlConditionDouble(string condition, double value)
        {
            string temp = " " + condition + "=" + value + " and ";
            return value == 0 ? "" : temp;
        }
    }
}
